﻿using Excel = Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace ParserColumnasDescripcionMarca
{

    public class CustomExcelSheet
    {
        public int Index { get; private set; }
        public string Name { get; private set; }

        public CustomExcelSheet(int _Index, DataRow dr)
        {
            Index = _Index;
            Name = dr.ItemArray[2].ToString().Replace("$", "");
        }
    }

    public class CustomExcelColumn
    {
        public int Index { get; private set; }
        public string Name { get; private set; }

        public CustomExcelColumn(int _Index, DataColumn dr)
        {
            Index = _Index;
            Name = dr.ColumnName;
        }
    }
    public class CustomExcelData
    {

        public CustomExcelData(DataRow dr)
        {
            try
            {
                GRUPO_COD = (string.IsNullOrEmpty(dr["GRUPO_COD"].ToString())
                    ? int.MinValue
                    : int.Parse(dr["GRUPO_COD"].ToString().Trim()));
            }
            catch
            {
                GRUPO_COD = int.MinValue;
            }
            GRUPO_DESC = dr["GRUPO_DESC"].ToString().Trim();
            try
            {
                FLIA_COD = (string.IsNullOrEmpty(dr["FLIA_COD"].ToString().Trim())
                    ? int.MinValue
                    : int.Parse(dr["FLIA_COD"].ToString().Trim()));
            }
            catch
            {
                FLIA_COD = int.MinValue;
            }
            FLIA_DESC = dr["FLIA_DESC"].ToString().Trim();
            try
            {
                SUB_FLIA_COD = (string.IsNullOrEmpty(dr["SUB_FLIA_COD"].ToString().Trim())
                    ? int.MinValue
                    : int.Parse(dr["SUB_FLIA_COD"].ToString().Trim()));
            }
            catch
            {
                SUB_FLIA_COD = int.MinValue;
            }
            SUB_FLIA_DESC = dr["SUB_FLIA_DESC"].ToString().Trim();
            try
            {
                COD = (string.IsNullOrEmpty(dr["COD"].ToString().Trim())
                    ? int.MinValue
                    : int.Parse(dr["COD"].ToString().Trim()));
            }
            catch
            {
                COD = int.MinValue;
            }
            GRUPO_DESC = dr["GRUPO_DESC"].ToString().Trim();
            FLIA_DESC = dr["FLIA_DESC"].ToString().Trim();
            SUB_FLIA_DESC = dr["SUB_FLIA_DESC"].ToString().Trim();
            DESC_COMP = dr["DESC_COMP"].ToString().Trim();
            DESC_LTA = dr["DESC_LTA"].ToString().Trim();
            PRES = dr["PRES"].ToString().Trim();
            try
            {
                MAR_COD = (string.IsNullOrEmpty(dr["MAR_COD"].ToString().Trim())
                    ? int.MinValue
                    : int.Parse(dr["MAR_COD"].ToString().Trim()));
            }
            catch
            {
                MAR_COD = int.MinValue;
            }
            MAR = dr["MAR"].ToString().Trim();
            try
            {
                PRECIO = (string.IsNullOrEmpty(dr["PRECIO"].ToString().Trim())
                    ? double.MinValue
                    : double.Parse(dr["PRECIO"].ToString().Trim()));
            }
            catch
            {
                PRECIO = double.MinValue;
            }
            IMP_INT = dr["IMP_INT"].ToString().Trim();
            OF = dr["OF"].ToString().Trim();
            CLAS_LTA = dr["CLAS_LTA"].ToString().Trim();
            V_LTA = dr["V_LTA"].ToString().Trim();

        }
        public int GRUPO_COD { get; private set; }
        public string GRUPO_DESC { get; private set; }
        public int FLIA_COD { get; private set; }
        public string FLIA_DESC { get; private set; }
        public int SUB_FLIA_COD { get; private set; }
        public string SUB_FLIA_DESC { get; private set; }
        public int COD { get; private set; }
        public string DESC_COMP { get; private set; }
        public string DESC_LTA { get; private set; }
        public string PRES { get; private set; }
        public int MAR_COD { get; private set; }
        public string MAR { get; private set; }
        public double PRECIO { get; private set; }
        public string IMP_INT { get; private set; }
        public string OF { get; private set; }
        public string CLAS_LTA { get; private set; }
        public string V_LTA { get; private set; }
        public string COMBINADA { get; set; }
    }

    public class ExcelApp : IDisposable
    {
        OleDbConnection connection = new OleDbConnection();

        public string ExcelPath { get; private set; }

        public List<CustomExcelSheet> ExcelSheets { get; private set; }

        public CustomExcelSheet SelectedSheet { get; private set; }

        public List<CustomExcelData> ExcelData { get; private set; }

        public ExcelApp(string _ExcelPath)
        {
            try
            {
                ExcelPath = _ExcelPath;
                ExcelSheets = GetSheets();
            }
            finally { Dispose(); }
        }

        private List<CustomExcelSheet> GetSheets()
        {
            List<CustomExcelSheet> MyList = new List<CustomExcelSheet>();
            connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelPath + ";Extended Properties='Excel 12.0 xml;HDR=YES;'");
            connection.Open();
            var Sheets = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            for (int i = 0; i < Sheets.Rows.Count; i++)
                if (!Sheets.Rows[i].ItemArray[2].ToString().Contains("FilterDatabase"))
                    MyList.Add(new CustomExcelSheet(i, Sheets.Rows[i]));

            return MyList;
        }

        public List<CustomExcelColumn> GetColumns(string SheetName)
        {
            SelectedSheet = ExcelSheets.Where(x => x.Name == SheetName).SingleOrDefault();

            connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelPath + ";Extended Properties='Excel 12.0 xml;HDR=YES;'");
            connection.Open();
            List<CustomExcelColumn> MyList = new List<CustomExcelColumn>();
            string query = string.Format("SELECT * FROM [{0}$]", SheetName);
            OleDbCommand cmd = new OleDbCommand(query, connection);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                MyList.Add(new CustomExcelColumn(i, ds.Tables[0].Columns[i]));

            return MyList;
        }

        public List<CustomExcelData> Process(string ColumnNameA, string ColumnNameB)
        {
            foreach (var item in ExcelData)
                item.COMBINADA = GetDesc(item, ColumnNameA, ColumnNameB);

            return ExcelData;
        }

        public List<string> GetExcelDataByColumn(string ColumnName)
        {
            List<string> MyList = new List<string>();

            var propertyInfo = typeof(CustomExcelData).GetProperty(ColumnName);

            var ColNotTyped = GetExcelData()
                .GroupBy(p => (propertyInfo.GetValue(p, null)))
                .Select(g => g.First())
                .ToList().Select(x => (propertyInfo.GetValue(x, null))).ToList();

            foreach (var item in ColNotTyped)
                MyList.Add(item.ToString());

            return MyList;
        }

        public List<CustomExcelData> GetExcelData()
        {
            List<CustomExcelData> MyList = new List<CustomExcelData>();
            string query = string.Format("SELECT * FROM [{0}$]", SelectedSheet.Name);
            OleDbCommand cmd = new OleDbCommand(query, connection);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            foreach (DataRow item in ds.Tables[0].Rows)
                MyList.Add(new CustomExcelData(item));

            ExcelData = MyList;
            return MyList;
        }

        public List<CustomExcelData> GetExcelData(string ColumnName, string value, bool OnlyProperty)
        {
            var propertyInfo = typeof(CustomExcelData).GetProperty(ColumnName);

            if (OnlyProperty)
                return ExcelData.Where(p => (propertyInfo.GetValue(p, null)).ToString() == value).ToList();
            else
                return ExcelData.Where(p => (propertyInfo.GetValue(p, null)).ToString() != value).ToList();
        }


        private string GetDesc(CustomExcelData item, string ColumnA, string ColumnB)
        {
            var ValueA = item.GetType().GetProperty(ColumnA).GetValue(item, null);
            var ValueB = item.GetType().GetProperty(ColumnB).GetValue(item, null);

            if (string.IsNullOrEmpty(ValueB.ToString()))
                return ValueA.ToString();

            return (ValueB.ToString().Trim() + " " + ValueA.ToString()
                     .Trim()
                     .ToUpper()
                     .Replace(ValueB.ToString().Trim().ToUpper(), ""))
                     .Trim()
                     .Replace('-', ' ')
                     .Replace("                          ", "")
                     .Replace("                         ", "")
                     .Replace("                        ", "")
                     .Replace("                       ", "")
                     .Replace("                      ", "")
                     .Replace("                     ", "")
                     .Replace("                    ", "")
                     .Replace("                   ", "")
                     .Replace("                  ", "")
                     .Replace("                 ", "")
                     .Replace("                ", "")
                     .Replace("               ", "")
                     .Replace("              ", "")
                     .Replace("             ", "")
                     .Replace("            ", "")
                     .Replace("           ", "")
                     .Replace("          ", "")
                     .Replace("         ", "")
                     .Replace("        ", "")
                     .Replace("       ", "");

        }

        public void Dispose()
        {
            connection.Close();
        }
    }
}
